import pandas as pd
import os
import xlwings as xw

app = xw.App(visible=False, add_book=False)
for file in os.listdir("销售表"):
    if file.endswith(".xlsx") and not file.startswith("~$"):
        workbook = app.books.open(f"销售表/{file}")
        worksheet = workbook.sheets[0]
        df = worksheet.range("A1").options(
            pd.DataFrame, expand='table').value
        df["销售数量"] = df["销售数量"].astype(float)
        df_agg = df.groupby("销售区域")["销售数量"].sum()
        worksheet.range("J1").value = df_agg
        workbook.save()
        workbook.close()
app.quit()
